<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <title>excel转换成sql ,by saruri</title>
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <link rel="stylesheet" type="text/css" href="https://www.layuicdn.com/layui/css/layui.css" />
    <!-- 注意：如果你直接复制所有代码到本地，上述css路径需要改成你本地的 -->
    <style>
        @media screen and (max-width:600px) {
            .td {
                margin-top: 1em;
            }

            .td2 {
                margin-bottom: 1em;
            }

            .td textarea {
                width: 14.7rem;
            }

            .cut {
                margin-top: 10px;
                margin-left: 0;
            }
        }

        .layui-textarea {
            min-height: 200px;
            background-color: #000;
            color: #fff;
        }

        #sql {
            font-size: xx-small;
        }

        .field-input {
            width: 60px;
        }

        #view {
            margin-left: 40px;
            background-color: #fff;
            min-height: 60px;
        }

        #view .layui-input-inline {
            width: 70px;
        }

        #view .layui-form-label {
            width: auto;
        }

        .layui-form-item .w240 {
            width: 600px;
        }

        .layui-form-item .w360 {
            width: 360px;
        }
    </style>
</head>

<body>

    <blockquote class="layui-elem-quote layui-text">
        excel转换成sql用的工具 大数据量插入用的 开源地址: https://gitee.com/saruri/ex
    </blockquote>

    <fieldset class="layui-elem-field layui-field-title" style="margin-top: 20px;">
        <legend> excel转换成sql</legend>
    </fieldset>



    <form class="layui-form" action="" id="excelForm">
        <div class="layui-form-item">
            <div class="layui-inline">
                <label class="layui-form-label">类型选择</label>
                <div class="layui-input-block">
                    <input type="radio" name="type" value="head" title="表头匹配" checked="">
                    <input type="radio" name="type" value="md5" title="md5匹配" disabled="">

                </div>
            </div>
            <div class="layui-inline">
                <label class="layui-form-label">表头位置 </label>
                <div class="layui-input-block">
                    <input type="radio" name="headLine" value="1" title="第一行" checked="checked">
                    <input type="radio" name="headLine" value="2" title="第二行">
                    <input type="radio" name="headLine" value="3" title="第三行">

                </div>

            </div>
        </div>


        <div class="layui-form-item">
            <div class="layui-inline">
                <label class="layui-form-label">表名</label>
                <div class="layui-input-inline">
                    <input type="text" name="tab" id="tab" placeholder="请输入表名" autocomplete="off" class="layui-input">
                </div>
                <div class="layui-form-mid layui-word-aux">请填写要插入的表名</div>
            </div>

            <div class="layui-inline">
                <label class="layui-form-label">配置名</label>
                <div class="layui-input-inline">
                    <input type="text" name="config" id="config" placeholder="请输入配置名" autocomplete="off"
                        class="layui-input">
                </div>
                <!-- <div class="layui-form-mid layui-word-aux"></div> -->
                <button type="button" class="layui-btn config-btn  lay-filter=" demo1">保存新配置</button>
            </div>


        </div>

        <div class="layui-form-item">
            <div class="layui-inline w360" style="display:none;">
                <label class="layui-form-label">md5值</label>
                <div class="layui-input-inline">
                    <input type="text" name="md5" id="md5" placeholder="请输入md5" autocomplete="off" class="layui-input">
                </div>

            </div>
            <div class="layui-inline">
                <label class="layui-form-label">表头</label>
                <div class="layui-input-inline w240">
                    <input type="text" name="head" id="head" placeholder="请输入head" autocomplete="off"
                        class="layui-input">
                </div>
            </div>



        </div>





        <!-- 
        <div class="layui-col-md4 layui-form-text td" style="display:none;">
            <label class="layui-form-label">markdown代码</label>

            <div class="layui-input-block">
                <textarea placeholder="请输入内容markdown格式" name="md" id="md" class="layui-textarea"
                    style="margin-right:2em;"># md格式演示表  isntall
项目 | 类型|说明
---|---|---
retaile_id| int 11  key auto|id
parent_userid | int 8|上级用户 
sub_userid|int 8 |下级用户
retaile_reg_type| tinnyint 1 | 0 二维码 ,1 链接
retaile_time| int 11|时间</textarea>
            </div>
        </div> -->
        <div class="layui-form-item">

            <fieldset class="layui-elem-field layui-field-title" style="margin-top: 10px;">
                <legend> 投递进度</legend>
            </fieldset>
            <div class="layui-progress" lay-showPercent="yes" lay-filter="send">
                <div class="layui-progress-bar layui-bg-red" lay-percent="0%"></div>
            </div>
        </div>
        <div class="layui-form-item">
            <fieldset class="layui-elem-field layui-field-title" style="margin-top: 10px;">
                <legend> 数据处理进度</legend>
            </fieldset>
            <div class="layui-progress" lay-showPercent="yes" lay-filter="data">
                <div class="layui-progress-bar layui-bg-green" lay-percent="0%"></div>
            </div>
        </div>
        <div class="layui-col-md4 layui-form-text td td2" style="width: 45.3333%;">
            <label class="layui-form-label">SQL文本</label>
            <div class="layui-input-block">
                <textarea placeholder="建表sql的文本" type="text/plain" id="sql" class="layui-textarea"></textarea>
            </div>
        </div>

        <!-- 文本框 -->
        <div class="layui-form-item" style="padding-top: 10px; ">
            <div class="layui-input-block">
                <button type="button" class="layui-btn" id="uploadExcel">
                    <i class="layui-icon">&#xe67c;</i>上传EXCEL文件
                </button>
                <!-- <button type="button" class="layui-btn sql  lay-filter=" demo1" style="margin-left: 10px;">立即提交</button> -->
                <button type="button" class="layui-btn send" style="margin-left: 10px;">发送</button>
                <!-- <button type="reset" class="layui-btn layui-btn-primary">重置内容</button> -->
                <button type="button" class="layui-btn layui-btn-primary cut" data-clipboard-action="cut"
                    data-clipboard-target="#sql" style="margin-left: 10px;">复制到剪贴板</button>
            </div>
        </div>

        <div class="layui-form-item" style="padding-top: 10px;">

            <div class="layui-inline w360">
                <label class="layui-form-label">消息：</label>
                <div class="layui-input-inline">
                    <input type="text" name="message" id="message" placeholder="请输消息" autocomplete="off"
                        class="layui-input">
                </div>
            </div>

            <div class="layui-inline w360">
                <label class="layui-form-label">接收者：</label>
                <div class="layui-input-inline">
                    <input type="text" name="to" id="to" placeholder="接受者" autocomplete="off" class="layui-input">
                </div>
            </div>

            <div class="layui-inline w360">

            </div>
        </div>

        <!-- 既有配置 -->
        <div class="layui-form-item">
            <div id="view1"></div>
        </div>
        <!-- 配置 -->
        <div class="layui-form-item">
            <div id="view"></div>

        </div>



        <div class="layui-form-item" style="padding-top: 10px;">
            <div class="layui-inline w360">
                <label class="layui-form-label">分页数据：</label>
                <div class="layui-input-inline">
                    <input type="text" name="pages" id="pages" placeholder="请输分页" autocomplete="off"
                        class="layui-input">
                </div>
            </div>
        </div>
    </form>





    <script src="https://www.layuicdn.com/layui/layui.js"></script>
    <!-- <script src="https://cdn.bootcss.com/clipboard.js/2.0.6/clipboard.js"></script> -->
    <!-- <script src="https://cdn.bootcdn.net/ajax/libs/jquery.serializeJSON/3.1.1/jquery.serializejson.js"></script> -->
    <script src="/static/clipboard.js"></script>
    <!-- <script src="/static/jquery.serializejson.js"></script> -->
    <script id="demo" type="text/html">
        <h3>{{ d.title }} 注意留空即为不启用该字段 例如 序号等</h3>
        {{#  layui.each(d.head, function(index, item){ }}
        <div class="layui-inline">
            <label class="layui-form-label">{{ item.val }}/{{ item.letter || '' }}</label>
            <div class="layui-input-block">
               
                <input type="text" name="Field_{{ item.letter || '' }}"  id="Field_{{ item.letter || '' }}"placeholder="字段：{{ item.field || ''}}" autocomplete="off" class="layui-input field-input">
            </div>
            <!-- <div class="layui-form-mid layui-word-aux">{{ item.letter || '' }}</div> -->
        </div>
        {{#  }); }}
        {{#  if(d.head.length === 0){ }}
          无数据
        {{#  } }} 
        </ul>
    </script>

    <script id="demo1" type="text/html">
        <h3>现有配置</h3>
       
        {{#  layui.each(d.configList, function(index, item){ }}
        <div class="layui-input-inline">
            <div class="layui-input-block">
               
                <input type="radio" name="configList" config="{{ item.config }}" tab="{{ item.tab }}" id="configList_{{ item.id }}" value="{{ item.content }}" lay-filter="ChoiceRadio" title="{{ item.config}}" >
            </div>
          
        </div>
        {{#  }); }}
      
        {{#  if(d.head.length === 0){ }}
          无数据
        {{#  } }} 
        </ul>
      </script>


    <script>



        layui.use(['form', 'layedit', 'laydate', 'upload', 'laytpl', 'element'], function () {
            var form = layui.form
                , layer = layui.layer
                , layedit = layui.layedit
                , laydate = layui.laydate
                , $ = layui.$;

            var upload = layui.upload;
            var laytpl = layui.laytpl;
            var element = layui.element;



            serializeObject = function (form) {
                var o = {};
                $.each($(form).serializeArray(), function (index) {

                    if (o[this['name']]) {
                        o[this['name']] = o[this['name']] + "," + this['value'];
                    } else {
                        o[this['name']] = this['value'];
                    }

                });
                return o;
            };



            //执行实例
            var uploadInst = upload.render({
                elem: '#uploadExcel' //绑定元素
                //, url: 'http://ex.sianwan.com/index/index/upload/' //上传接口
                , url: 'https://ex.saruri.cn/index/index/upload/' //上传接口
                , field: 'file'
                , exts: 'xls|xlsx'
                ,
                before: function (obj) {
                    this.data = { 'headLine': $('input[name="headLine"]:checked').val() };//关键代码
                }
                , done: function (res) {
                    //上传完毕回调
                    console.log("上传成功");
                    makeField(res);
                }
                , error: function () {
                    //请求异常回调
                    console.log("上传失败");
                }
            });

            //构造字段列
            function makeField(res) {
                //alert("渲染成功");
                //console.log(res.data);
                //第三步：渲染模版
                var data1 = { //数据
                    "title": "Layui常用模块"
                    , "list": [{ "modname": "弹层", "alias": "layer", "site": "layer.layui.com" }, { "modname": "表单", "alias": "form" }]
                }
                let data = res.data;

                var getTpl = demo.innerHTML
                    , view = document.getElementById('view');
                laytpl(getTpl).render(data, function (html) {
                    view.innerHTML = html;
                });



                //element.init();//一定要加上这句话，否者不行！！！！
                insertFieldConfigName(data);
                insertMd5(data);
                insertHead(data);
                insertConfigList(data);
                insertPages(data.pages);
                //var pages=data.pages;
            }

            function insertPages(pages) {
                //console.log(pages);
                $("#pages").data("page", pages);
            }

            function insertConfigList(data) {
                //console.log("渲染既有配置");
                //console.log(data.configList);
                //console.log(data);
                var getTpl = demo1.innerHTML
                    , view1 = document.getElementById('view1');
                laytpl(getTpl).render(data, function (html) {
                    view1.innerHTML = html;
                });
                form.render();
            }
            //填充默认配置名称
            function insertFieldConfigName(data) {
                let timestamp = new Date().getTime();
                let configTitle = data.title + '_' + timestamp;
                $("#config").val(configTitle);
                //alert("添加默认配置名称");
            }

            //填写md5值
            function insertMd5(data) {
                let fileMd5 = data.md5;
                $("#md5").val(fileMd5);
                // alert("更新了md5值");
            }

            function insertHead(data) {
                let str = '';

                for (let key in data.head) {
                    //console.log(key + '---' + obj[key])
                    str += data.head[key].val + '|';
                }

                $("#head").val(str);




            }

            //获取填写的字段对应的数据
            function getFields() {
                let name = '';
                let fields = new Array()
                $(".field-input").each(function () {
                    //alert($(this).text())
                    name = $(this).attr('name');
                    if (name != '') {
                        fields[name] = $(this).val();
                    }
                });
                //console.log("测试");
                // console.log(fields);
                //return 111;
                return fields;
            }

            //填写字段配置
            function inserFieldConfig(content) {
                //alert("填写表单");
                //let content = data.elem.getAttribute('content');
                //console.log(content);
                var strs = new Array(); //定义一数组
                let arr = new Array(); //定义一数组
                strs = content.split(","); //字符分割
                //console.log("填写字段配置开始");
                for (i = 0; i < strs.length; i++) {
                    //document.write(strs[i] + "<br/>"); //分割后的字符输出
                    arr = strs[i].split("|");


                    if (typeof (arr[1]) != "undefined") {
                        //alert("d为undefined");
                        //console.log(arr[0] + '|' + arr[1]);
                        $("#" + arr[0]).val(arr[1]);
                    }

                    //console.log(strs[i]);
                }
                // console.log("填写字段配置结束");

            }

            //保存配置
            $('.config-btn').on('click', function () {



                let config = $("#config").val();
                let tab = $("#tab").val();
                let md5 = $("#md5").val();
                let head = $("#head").val();
                //var fields=getFields();

                let name = '';
                let str = '';
                //var fields=new Array()
                $(".field-input").each(function () {
                    //alert($(this).text())
                    name = $(this).attr('name');
                    if (name != '') {
                        //fields[name]=$(this).val();
                        str += name + '|' + $(this).val() + ',';
                    }
                });

                // console.log(fields);

                var url = "/index/index/saveConfig";
                $.ajax({
                    async: false,
                    type: "Post",
                    url: url,
                    dataType: "json",
                    traditional: true,//必须加上设置为true
                    data: { 'config': config, 'tab': tab, 'md5': md5, 'fields2': str, 'head': head },
                    success: function (data) {
                        //$("#sql").val(data.data);
                        alert("保存成功");

                    }
                });




            });


            $('.sql').on('click', function () {
                var md = $("#md").val();

                let textarea = form.val("md");

                $("#sql").val('');
                var url = "https://api.saruri.cn/base.php";
                $.ajax({
                    async: false,
                    type: "Post",
                    url: url,
                    dataType: "json",
                    data: { md: md },
                    success: function (data) {
                        $("#sql").val(data.data);
                        var clipboard = new ClipboardJS('.cut');
                        clipboard.on('success', function (e) {
                            //alert('复制成功');
                            layer.alert('复制成功', {
                                title: '把SQL复制到剪贴板'
                            })
                            clipboard.destroy();  //解决多次弹窗
                            e.clearSelection();
                        });

                    }
                });




            });

            form.on('radio(ChoiceRadio)', function (data) {
                console.log(data.elem.getAttribute('tab')); //得到 radio 原始 DOM 对象
                let tab = data.elem.getAttribute('tab');
                let config = data.elem.getAttribute('config');
                let content = data.elem.getAttribute('value');
                $("#tab").val(tab);
                $("#config").val(config);
                inserFieldConfig(content);

                console.log(data.value); //被点击的 radio 的 value 值
            });

            var ws = new WebSocket("ws://122.51.11.209:9604");
           // ssh://root@122.51.11.209:5198
            //var ws = new WebSocket("ws://122.14.217.181:19385");

            //var ws = new WebSocket("ws://122.51.11.209:9501");
            //var ws = new WebSocket("ws://122.14.217.181:9501");
            ws.onopen = function () {
                console.log('连接成功');
            }

            //数据返回的解析
            function mycallback(data) {
                var start = data.indexOf('[') // 第一次出现的位置
                var start1 = data.indexOf('{')

                if (start < 0) {
                    start = start1;
                }
                if (start >= 0 && start1 >= 0) {
                    start = Math.min(start, start1);
                }

                if (start >= 0) {
                    console.log(data);
                    var json = data.substr(start); //截取
                    var json = JSON.parse(json);
                    reflex(json);
                    $("#sql").append(data + "\r\n");
                    console.log("触发回调事件");
                    console.log(json);
                }
            }


            function reflex(json) {

                //没有策略模式?
                if (json['event'] == 'sendMqSuccess') {
                    sendSuccess(json);
                }

                if (json['event'] == 'dataProgress') {
                    dataSuccess(json);
                }

            }

            function dataSuccess(json) {
                element.progress('data', '90%');
            }

            ws.onmessage = function (data) {
                console.log(data.data);
                console.log("触发onmessage事件");
                //sendSuccess(data.data);
                mycallback(data.data);
            }

            ws.onclose = function () {
                console.log('连接断开');
                alert("调用了断开连接");
            }

            $('.send').on('click', function () {
                send();
            });

            function sendSuccess(json) {
                console.log(json['messageBody']['page'].num);
                let num=json['messageBody']['page'].num;
                let per=json['perPage'];
                //console.log(per+'|'+num);
                let result=parseInt(num)*parseInt(per);
                if(result>100){
                    result=100;
                }
                element.progress('send', result+'%');

            }



            function send() {
                //var message = document.getElementById('message').value;
                let page = $("#pages").data("page");
                //console.log("pages");
                //console.log(page);
                //var message = $('#excelForm').serializeJSON();
                var message = serializeObject($('#excelForm'));
                message.page = page;
                //console.log(message);
                //return false;
                var to = document.getElementById('to').value;
                //console.log("准备给" + to + "发送数据：" + message);
                ws.send(JSON.stringify(['test', {
                    to: to,
                    message: message
                }])); //发送的数据必须是 ['test',数据] 这种格式
            }


        });



    </script>



    <script>
        //var ws = new WebSocket("ws://122.51.11.209:9501");



    </script>



</body>

</html>